import pandas as pd
from pymysql.converters import escape_string

import pymysql
import asyncio
import jieba

# 打开数据库连接
from aiohttp import ClientSession

db_config = {
    'host': '3a524389i9.qicp.vip',  # 主机
    'user': 'root',  # 用户名
    'password': 'zjdx20210126',  # 密码
    'port': 63307,  # 端口 3306
    'database': 'uni_center'  # 数据库名
}


async def main():
    word_book_df = pd.DataFrame(columns=['book_id', 'word', 'book_name'])
    user_word_book_df = pd.DataFrame(columns=['user_id', 'user_name', 'book_id', 'book_name', 'sort', 'version'])

    jieba.load_userdict("C:\\Users\\Administrator\\Desktop\\联创接口\\zjdx.txt")
    # seg_list = jieba.cut("我来到北京清华大学", cut_all=True)
    # print("Full Mode: " + "/ ".join(seg_list))  # 全模式
    db = pymysql.connect(**db_config)
    # 使用cursor()方法获取操作游标a
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select max(id),uni_id,max(book_name),max(org_id) from c_book202305241100 GROUP BY uni_id"
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        index = 0
        for row in results:
            book_id = row[0]
            uni_id = row[1]
            book_name = row[2]
            org_id = row[3]
            seg_list = jieba.cut(book_name, cut_all=False)
            # print("Full Mode: " + "/ ".join(seg_list))  # 全模式
            for seg in seg_list:
                if len(seg) >= 2:
                    print(seg)
                    word_book_df.loc[index] = [book_id, seg, book_name]
                    index += 1

    except Exception as m:
        print(m)
    # 关闭数据库连接
    db.close()
    print(f"书名分词已完成 ，总数-> {word_book_df.size}。")

    print(f"用户关键词开始。")
    xlsx_df = pd.read_excel("C:\\Users\\Administrator\\Desktop\\联创接口\\专家关键词数据.xlsx")
    ids = pd.to_numeric(xlsx_df['ID'])
    names = xlsx_df['姓名']
    word = xlsx_df['关键词']
    for i, d in ids.items():
        name = names[i]
        user_id = ids[i]
        if isinstance(word[i], str):
            words = word[i].split(';')
            for w in words:
                print(f"name -> {name} word -> {w}")
                if len(w) > 2:
                    seg_list = jieba.cut(w, cut_all=False)
                    for seg in seg_list:
                        if len(seg) >= 2:
                            # df.query("(column_name1 == ‘str1’) & (column_name2 == ‘str2’)")
                            print(f"-------------------------------{seg}")
                            word_book_df_search = word_book_df.loc[word_book_df['word'] == seg]
                            print(word_book_df_search)
                            for index, row in word_book_df_search.iterrows():
                                book_id = row["book_id"]
                                book_name = row["book_name"]
                                user_word_book_df_search = user_word_book_df.loc[(user_word_book_df['user_id'] == user_id) & (user_word_book_df['book_id'] == book_id)]
                                if user_word_book_df_search.size == 0:
                                    user_word_book_df_new = pd.DataFrame([{
                                        'user_id': user_id,
                                        'user_name': name,
                                        'book_id': book_id,
                                        'book_name': book_name,
                                        'sort': 1,
                                        'version': 0,
                                    }])
                                    user_word_book_df = pd.concat([user_word_book_df, user_word_book_df_new], ignore_index=True)
                                else:
                                    for user_word_book_index, user_word_book_row in user_word_book_df_search.iterrows():
                                        sort = user_word_book_row["sort"]
                                        sort += 1
                                        user_word_book_df.loc[user_word_book_index, "sort"] = sort

                else:
                    word_book_df_search = word_book_df.loc[word_book_df['word'] == w]
                    print(word_book_df_search)
                    for index, row in word_book_df_search.iterrows():
                        book_id = row["book_id"]
                        book_name = row["book_name"]
                        print(index)
                        user_word_book_df_search = user_word_book_df.loc[
                            (user_word_book_df['user_id'] == user_id) & (user_word_book_df['book_id'] == book_id)]
                        if user_word_book_df_search.size == 0:
                            user_word_book_df_new = pd.DataFrame([{
                                'user_id': user_id,
                                'user_name': name,
                                'book_id': book_id,
                                'book_name': book_name,
                                'sort': 1,
                                'version': 0,
                            }])
                            user_word_book_df = pd.concat([user_word_book_df, user_word_book_df_new], ignore_index=True)
                        else:
                            for user_word_book_index, user_word_book_row in user_word_book_df_search.iterrows():
                                sort = user_word_book_row["sort"]
                                sort += 1
                                user_word_book_df.loc[user_word_book_index, "sort"] = sort

        else:
            continue

    print(user_word_book_df)

    user_word_book_df.to_sql()
    user_word_book_df.to_excel('C:\\Users\\Administrator\\Desktop\\联创接口\\c_book202305241100.xlsx', sheet_name='sheet1', index=False)


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())
